﻿using Dapper;
using OddUserManager.Data.IRepositories;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OddUserManager.Data.Repositories
{
    public class BookMarkRepository : DapperBaseRepository<BookMark, int>, IBookMarkRepository
    {
        public List<BookMark> GetListOrderByIndex()
        {
            var sql = $"select * from BookMark order by MarkIndex desc ";
            using (var con = GetOpenConnection())
            {
                return con.Query<BookMark>(sql, null, null, true, 3000, null).AsList();
            }
        }

        public bool InsertOrUpdate(BookMark entity)
        {
            var t = Get(entity.Id);
            if (null == t)
            {
                return Insert(entity);
            }
            else
            {
                return Update(entity);
            }
        }

        public IEnumerable<BookMark> LoadPageList(int startPage, int pageSize, out int rowCount, Func<BookMark, bool> where, Func<BookMark, object> order)
        {
            var result = GetAllList();
            if (where != null)
                result = result.Where(where);
            if (order != null)
                result = result.OrderBy(order);
            else
                result = result.OrderBy(m => m.Id);
            rowCount = result.Count();
            return result.Skip((startPage - 1) * pageSize).Take(pageSize);
        }

        public PageViewCollection<BookMark> SearchMarkPages(BookMark param, int pageIndex, int pageSize, string sortColumn, string sortDirection)
        {
            PageViewCollection<BookMark> collection = new PageViewCollection<BookMark>();
            if (IsStringNull(sortColumn))
            {
                sortColumn = "MarkIndex";
            }
            if (IsStringNull(sortDirection))
            {
                sortDirection = "asc";
            }
            using (var con = GetOpenConnection())
            {
                var countHeadSql = " select count(id) from BookMark where 1=1 ";
                var pageHeadSql = $" select top {pageSize} * from (select ROW_NUMBER() OVER ( ORDER BY {sortColumn} {sortDirection} ) AS rownumber ,(select UserName from AppUser where Id = t.CreateAppUserId) as CreateAppUserName,(select UserName from AppUser where Id = t.ModAppUserId) as ModAppUserName, t.* from BookMark t where 1=1 ";
                var conditionSql = " ";
                if (param != null)
                {
                    if (!IsStringNull(param.MarkName))
                    {
                         
                        conditionSql += $" and MarkName like '%{SwapSqlStr(param.MarkName)}%' ";
                    }
                    if (!IsStringNull(param.MarkColor))
                    {
                        conditionSql += $" and MarkColor like '%{SwapSqlStr(param.MarkColor)}%' ";
                    }
                    
                }

                // 获取记录总数
                collection.TotalCount = con.Query<int>($" {countHeadSql} {conditionSql} ").FirstOrDefault();
                collection.PageIndex = pageIndex;
                collection.PageSize = pageSize;
                collection.PageCount = Math.Ceiling(Convert.ToDecimal(collection.TotalCount) / pageSize);

                var pageFootSql = $" ) A WHERE rownumber > {(pageIndex - 1) * pageSize} ";
                collection.Result = con.Query<BookMark>($" {pageHeadSql} {conditionSql} {pageFootSql}");

            }

            return collection;
        }


        private bool IsStringNull(Object obj)
        {
            if (obj == null) return true;
            if (obj.ToString().Trim().Length < 1) return true;
            return false;
        }

        private string SwapSqlStr(string str) {
            if (IsStringNull(str)) return str;

            str = str.Replace(";","");
            str = str.Replace("'","");
            str = str.Replace("/","");
            str = str.Replace("&","&amp;");
            str = str.Replace("<","&lt;");
            str = str.Replace(">","&gt;");
            str = str.Replace("--","");
            str = str.Replace("%","");

            return str;
        }
    }
}
